const connection = require('../app/database');

class UserService {
  // 获取用户表内的数据总量
  async getUserCount() {
    try {
      const statement = `
        SELECT COUNT(*) AS total FROM user WHERE valid = 1;
      `;
      const [result] = await connection.execute(statement);
      console.log('[user.service_getUserCount]{success}', result);
      return result[0]['total'];
    } catch(e) {
      console.log('[user.service_getUserCount]{fail}', e);
    }
  }


  // 获取用户列表
  async getUserList(arg) {
    try {
      let dataArr = [], sql = '';
      let dataObj = { name: arg.name, identify: arg.identify };
      for (let key in dataObj) {
        if (dataObj[key] !== '') {
          sql += ` AND ${key} = ?`;
          dataArr.push(dataObj[key]);
        }
      }
      dataArr.push(arg.page, arg.size);
  
      const statement = `
        SELECT * FROM user WHERE 1=1 AND valid = 1${sql} LIMIT ?, ?;
      `;
      const [result] = await connection.execute(statement, dataArr);
      console.log('[user.service_getUserList]{success}', result);
      return result;
    } catch(e) {
      console.log('[user.service_getUserList]{fail}', e);
    }
  }

  // 新增用户
  async addUser(arg) {
    try {
      let keyArr = [], valueArr = [], valueQue = [];
      for (let key in arg) {
        keyArr.push(key);
        valueQue.push('?');
        valueArr.push(arg[key]);
      }
      const valueQueStr = valueQue.join(', ');
      const keyStr = keyArr.join(', ');
      const statement = `
        INSERT INTO user (${keyArr}) VALUES (${valueQueStr});
      `;
      const [result] = await connection.execute(statement, valueArr);
      console.log('[user.service_addUser]{success}', result);
      return true;
    } catch(e) {
      console.log('[user.service_addUser]{fail}', e);
    }
  }

  // 编辑用户
  async updateUser(arg) {
    try {
      let dataArr = [], sqlArr = [], sql = '';
      for (let key in arg) {
        if (key !== 'id') {
          sqlArr.push(`${key} = ?`);
          if (key === 'age' && arg[key] === '') { // age 为空时，将其值变为null
            dataArr.push(null);
          } else {
            dataArr.push(`${arg[key]}`);
          }
        }
      }
      sql = sqlArr.join(', ');
      dataArr.push(arg['id']);
      console.log(dataArr);
      const statement = `
        UPDATE user SET ${sql} WHERE id = ?;
      `;
      const [result] = await connection.execute(statement, dataArr);
      console.log('[user.service_updateUser]{success}', result);
      return true;
    } catch(e) {
      console.log('[user.service_updateUser]{fail}', e);
    }
  }

  // 失效用户数据
  async deleteUser(arg) {
    try {
      const statement = `
        UPDATE user SET valid = 0 WHERE id = ?;
      `;
      const [result] = await connection.execute(statement, [arg['id']]);
      console.log('[user.service_deleteUser]', result);
      return true;
    } catch(e) {
      console.log('[user.service_deleteUser]{fail}', e);
    }
  }
}

module.exports = new UserService();